OML4SQL Classification RF.dsnb

[{"layout":null,"template":null,"templateConfig":null,"name":"OML4SQL Classification RF","description":null,"readOnly":false,"type":"low","paragraphs":[{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":null,"title":null,"message":[],"enabled":true,"result":{"startTime":1715350480225,"interpreter":"md.low","endTime":1715350480303,"results":[],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":0,"hideResult":true,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","# Classification Modeling to Predict Target Customers using Random Forest","In this notebook, we predict customers most likely to be positive responders to an Affinity Card loyalty program. High Affinity Card responders (target value = 1) are defined as those customers who when given a loyaly or affinity card hyper-respond i.e. they increae their purchasing higher than the Affinity Card program's offered discount percentage. This notebook builds and applies classification random forest models using the SH schema data. All processing occurs inside Oracle Autonomous Database.","","Copyright (c) 2024 Oracle Corporation ","###### <a href=\"https://oss.oracle.com/licenses/upl/\" onclick=\"return ! window.open('https://oss.oracle.com/licenses/upl/');\">The Universal Permissive License (UPL), Version 1.0<\/a>","---"],"enabled":true,"result":{"startTime":1715350480379,"interpreter":"md.low","endTime":1715350480457,"results":[{"message":"<h1 id=\"classification-modeling-to-predict-target-customers-using-random-forest\">Classification Modeling to Predict Target Customers using Random Forest<\/h1>\n<p>In this notebook, we predict customers most likely to be positive responders to an Affinity Card loyalty program. High Affinity Card responders (target value = 1) are defined as those customers who when given a loyaly or affinity card hyper-respond i.e. they increae their purchasing higher than the Affinity Card program's offered discount percentage. This notebook builds and applies classification random forest models using the SH schema data. All processing occurs inside Oracle Autonomous Database.<\/p>\n<p>Copyright (c) 2024 Oracle Corporation<\/p>\n<h6 id=\"the-universal-permissive-license-upl-version-10\"><a href=\"https://oss.oracle.com/licenses/upl/\" onclick=\"return ! window.open('https://oss.oracle.com/licenses/upl/');\">The Universal Permissive License (UPL), Version 1.0<\/a><\/h6>\n<hr />\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":9,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","<dl>","<img src=\"http://www.oracle.com/technetwork/database/options/advanced-analytics/classification-5663162.jpg\" alt=\"OML Notebooks\" width=\"250\"/>","<\/dl>"],"enabled":true,"result":{"startTime":1715350480535,"interpreter":"md.low","endTime":1715350480613,"results":[{"message":"<dl>\n<img src=\"http://www.oracle.com/technetwork/database/options/advanced-analytics/classification-5663162.jpg\" alt=\"OML Notebooks\" width=\"250\"/>\n<\/dl>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":3,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":"For more information...","message":["%md","","* <a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/index.html\" target=\"_blank\">Oracle ADB Documentation<\/a>","* <a href=\"https://github.com/oracle-samples/oracle-db-examples/tree/main/machine-learning\" target=\"_blank\">OML folder on Oracle GitHub<\/a>","* <a href=\"https://www.oracle.com/machine-learning\" target=\"_blank\">OML Web Page<\/a>","* <a href=\"https://www.oracle.com/goto/ml-classification\" target=\"_blank\">OML Classification<\/a>","* <a href=\"https://oracle.com/goto/ml-random-forest\" target=\"_blank\">OML Random Forest<\/a>"],"enabled":true,"result":{"startTime":1715350480689,"interpreter":"md.low","endTime":1715350480749,"results":[{"message":"<ul>\n<li><a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/index.html\" target=\"_blank\">Oracle ADB Documentation<\/a><\/li>\n<li><a href=\"https://github.com/oracle-samples/oracle-db-examples/tree/main/machine-learning\" target=\"_blank\">OML folder on Oracle GitHub<\/a><\/li>\n<li><a href=\"https://www.oracle.com/machine-learning\" target=\"_blank\">OML Web Page<\/a><\/li>\n<li><a href=\"https://www.oracle.com/goto/ml-classification\" target=\"_blank\">OML Classification<\/a><\/li>\n<li><a href=\"https://oracle.com/goto/ml-random-forest\" target=\"_blank\">OML Random Forest<\/a><\/li>\n<\/ul>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display the SUPPLEMENTARY_DEMOGRAPHICS data ","message":["%sql","","SELECT * ","FROM SH.SUPPLEMENTARY_DEMOGRAPHICS","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1715350480827,"interpreter":"sql.low","endTime":1715350482429,"results":[{"message":"CUST_ID\tEDUCATION\tOCCUPATION\tHOUSEHOLD_SIZE\tYRS_RESIDENCE\tAFFINITY_CARD\tBULK_PACK_DISKETTES\tFLAT_PANEL_MONITOR\tHOME_THEATER_PACKAGE\tBOOKKEEPING_APPLICATION\tPRINTER_SUPPLIES\tY_BOX_GAMES\tOS_DOC_SET_KANJI\tCOMMENTS\n102547\t10th\tOther\t1\t0\t0\t1\t1\t0\t0\t1\t1\t0\t\n101050\t10th\tOther\t1\t0\t0\t1\t1\t0\t0\t1\t1\t0\t\n100040\t11th\tSales\t1\t0\t0\t1\t1\t0\t0\t1\t1\t0\t\n102117\tHS-grad\tFarming\t1\t0\t0\t0\t0\t0\t1\t1\t1\t0\t\n101074\t10th\tHandler\t1\t1\t0\t1\t1\t0\t0\t1\t1\t0\t\n104179\t10th\tHandler\t1\t1\t0\t1\t1\t0\t0\t1\t1\t0\t\n100417\t11th\tHandler\t1\t1\t0\t0\t0\t0\t0\t1\t1\t0\t\n101146\t< Bach.\t?\t1\t1\t0\t1\t1\t0\t1\t1\t1\t0\t\n103420\t< Bach.\t?\t1\t1\t0\t1\t1\t0\t1\t1\t1\t0\t\n101987\t< Bach.\tOther\t1\t1\t0\t1\t1\t0\t1\t1\t1\t0\t\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Count number of records in SUPPLEMENTARY_DEMOGRAPHICS table","message":["%sql","","SELECT COUNT(*) ","FROM SH.SUPPLEMENTARY_DEMOGRAPHICS;"],"enabled":true,"result":{"startTime":1715350482506,"interpreter":"sql.low","endTime":1715350482667,"results":[{"message":"COUNT(*)\n4500\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":5,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"table\":{\"version\":1},\"bar\":{\"showSeries\":[\"COUNT\"],\"aggregationOption\":\"Sum\",\"series\":{\"availableSeriesElements\":[{\"id\":\"COUNT\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"axis\":{\"x\":{\"title\":\"Response (1 = Responded, 0 = No Response)\"}},\"lastColumns\":[\"AFFINITY_CARD\",\"COUNT\"],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"bar","title":"Show distribution of AFFINITY_CARD responders","message":["%sql ","","SELECT AFFINITY_CARD, COUNT(*) COUNT ","FROM SH.SUPPLEMENTARY_DEMOGRAPHICS","GROUP BY AFFINITY_CARD;"],"enabled":true,"result":{"startTime":1715350482750,"interpreter":"sql.low","endTime":1715350482829,"results":[{"message":"AFFINITY_CARD\tCOUNT\n1\t1072\n0\t3428\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":7,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"bar\":{\"groupByColumns\":[\"AFFINITY_CARD\",\"HOUSEHOLD_SIZE\"],\"series\":{\"availableSeriesElements\":[{\"id\":\"NUM_CUSTOMERS\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"NUM_CUSTOMERS\",\"HOUSEHOLD_SIZE\",\"AFFINITY_CARD\"],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"bar","title":"Graph HOUSEHOLD_SIZE grouped by AFFINITY_CARD","message":["%sql","","SELECT COUNT(CUST_ID) AS NUM_CUSTOMERS, HOUSEHOLD_SIZE, AFFINITY_CARD ","FROM SH.SUPPLEMENTARY_DEMOGRAPHICS ","GROUP BY HOUSEHOLD_SIZE, AFFINITY_CARD;"],"enabled":true,"result":{"startTime":1715350482914,"interpreter":"sql.low","endTime":1715350482991,"results":[{"message":"NUM_CUSTOMERS\tHOUSEHOLD_SIZE\tAFFINITY_CARD\n2\t6-8\t1\n1040\t2\t0\n109\t2\t1\n476\t9+\t0\n29\t9+\t1\n814\t3\t1\n107\t4-5\t1\n112\t4-5\t0\n11\t1\t1\n973\t3\t0\n681\t1\t0\n146\t6-8\t0\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"bar\":{\"showSeries\":[\"AFFINITY_1_COUNT\",\"AFFINITY_0_COUNT\"],\"aggregationOption\":\"Last\",\"visualizations\":{\"stackValue\":\"on\"},\"series\":{\"availableSeriesElements\":[{\"id\":\"AFFINITY_1_COUNT\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0},{\"id\":\"AFFINITY_0_COUNT\",\"lineType\":\"straight\",\"borderColor\":\"rgb(50, 146, 94)\",\"borderWidth\":0,\"color\":\"rgb(50, 146, 94)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(50, 146, 94)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"HOUSEHOLD_SIZE\",\"AFFINITY_1_COUNT\",\"AFFINITY_0_COUNT\"],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"bar","title":"Graph HOUSEHOLD_SIZE grouped by AFFINITY_CARD","message":["%sql","","SELECT HOUSEHOLD_SIZE,"," SUM(CASE WHEN AFFINITY_CARD = 1 THEN NUM_CUSTOMERS ELSE 0 END) AS AFFINITY_1_COUNT,"," SUM(CASE WHEN AFFINITY_CARD = 0 THEN NUM_CUSTOMERS ELSE 0 END) AS AFFINITY_0_COUNT","FROM (SELECT COUNT(CUST_ID) AS NUM_CUSTOMERS, HOUSEHOLD_SIZE, AFFINITY_CARD "," FROM SH.SUPPLEMENTARY_DEMOGRAPHICS "," GROUP BY HOUSEHOLD_SIZE, AFFINITY_CARD)","GROUP BY HOUSEHOLD_SIZE","ORDER BY HOUSEHOLD_SIZE"],"enabled":true,"result":{"startTime":1715350483073,"interpreter":"sql.low","endTime":1715350483147,"results":[{"message":"HOUSEHOLD_SIZE\tAFFINITY_1_COUNT\tAFFINITY_0_COUNT\n1\t11\t681\n2\t109\t1040\n3\t814\t973\n4-5\t107\t112\n6-8\t2\t146\n9+\t29\t476\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Create view with desired columns","message":["%script","","CREATE OR REPLACE VIEW DEMOGRAPHICS4_V "," AS SELECT AFFINITY_CARD, BOOKKEEPING_APPLICATION,"," BULK_PACK_DISKETTES, CUST_ID, EDUCATION,"," FLAT_PANEL_MONITOR, HOME_THEATER_PACKAGE, "," HOUSEHOLD_SIZE, OCCUPATION, OS_DOC_SET_KANJI,"," PRINTER_SUPPLIES, YRS_RESIDENCE, Y_BOX_GAMES"," FROM SH.SUPPLEMENTARY_DEMOGRAPHICS;"," "],"enabled":true,"result":{"startTime":1715350483225,"interpreter":"script.low","endTime":1715350483724,"results":[{"message":"\nView DEMOGRAPHICS4_V created.\n\n\n---------------------------\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":4,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display data from DEMOGRAPHICS4_V view","message":["%sql ","","SELECT * ","FROM DEMOGRAPHICS4_V","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1715350483801,"interpreter":"sql.low","endTime":1715350483898,"results":[{"message":"AFFINITY_CARD\tBOOKKEEPING_APPLICATION\tBULK_PACK_DISKETTES\tCUST_ID\tEDUCATION\tFLAT_PANEL_MONITOR\tHOME_THEATER_PACKAGE\tHOUSEHOLD_SIZE\tOCCUPATION\tOS_DOC_SET_KANJI\tPRINTER_SUPPLIES\tYRS_RESIDENCE\tY_BOX_GAMES\n0\t0\t1\t102547\t10th\t1\t0\t1\tOther\t0\t1\t0\t1\n0\t0\t1\t101050\t10th\t1\t0\t1\tOther\t0\t1\t0\t1\n0\t0\t1\t100040\t11th\t1\t0\t1\tSales\t0\t1\t0\t1\n0\t1\t0\t102117\tHS-grad\t0\t0\t1\tFarming\t0\t1\t0\t1\n0\t0\t1\t101074\t10th\t1\t0\t1\tHandler\t0\t1\t1\t1\n0\t0\t1\t104179\t10th\t1\t0\t1\tHandler\t0\t1\t1\t1\n0\t0\t0\t100417\t11th\t0\t0\t1\tHandler\t0\t1\t1\t1\n0\t1\t1\t101146\t< Bach.\t1\t0\t1\t?\t0\t1\t1\t1\n0\t1\t1\t103420\t< Bach.\t1\t0\t1\t?\t0\t1\t1\t1\n0\t1\t1\t101987\t< Bach.\t1\t0\t1\tOther\t0\t1\t1\t1\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":8,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Create train and test data sets (60/40)","message":["%script","","CREATE OR REPLACE VIEW TRAIN_DATA_CLAS AS SELECT * FROM DEMOGRAPHICS4_V SAMPLE (60) SEED (1);","CREATE OR REPLACE VIEW TEST_DATA_CLAS AS SELECT * FROM DEMOGRAPHICS4_V MINUS SELECT * FROM TRAIN_DATA_CLAS;"],"enabled":true,"result":{"startTime":1715350483983,"interpreter":"script.low","endTime":1715350484078,"results":[{"message":"\nView TRAIN_DATA_CLAS created.\n\n\n---------------------------\n\nView TEST_DATA_CLAS created.\n\n\n---------------------------\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","### Build Random Forest model to Predict AFFINITY_CARD","","---"],"enabled":true,"result":{"startTime":1715350484160,"interpreter":"md.low","endTime":1715350484223,"results":[{"message":"<h3 id=\"build-random-forest-model-to-predict-affinity_card\">Build Random Forest model to Predict AFFINITY_CARD<\/h3>\n<hr />\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Build a Random Forest model using default settings","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('RF_CLASS_MODEL');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","DECLARE"," v_setlst DBMS_DATA_MINING.SETTING_LIST;"," ","BEGIN"," v_setlst('PREP_AUTO') := 'ON';"," v_setlst('ALGO_NAME') := 'ALGO_RANDOM_FOREST';",""," DBMS_DATA_MINING.CREATE_MODEL2("," MODEL_NAME => 'RF_CLASS_MODEL',"," MINING_FUNCTION => 'CLASSIFICATION',"," DATA_QUERY => 'SELECT * FROM TRAIN_DATA_CLAS',"," SET_LIST => v_setlst,"," CASE_ID_COLUMN_NAME => 'CUST_ID',"," TARGET_COLUMN_NAME => 'AFFINITY_CARD'"," );","END;"],"enabled":true,"result":{"startTime":1715350484300,"interpreter":"script.low","endTime":1715350486297,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","### Examples of possible setting overrides for Random Forest ","","If the user does not override the default settings, then relevant settings are determined by the algorithm.","","**NOTE:** Random Forest makes use of the Decision Tree settings to configure the construction of individual trees. ","","A complete list of settings can be found in the Documentation link:","","* <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-481B6C67-B26E-4689-AD4C-98062D5A2117\" target=\"_blank\">Algorithm Settings - Random Forest<\/a>","* <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-03435110-D723-42FD-B4EA-39C86A039566\" target=\"_blank\">Algorithm Settings - Decision Tree<\/a>","* <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75\" target=\"_blank\">Shared Settings<\/a>","","* Specify a row weight column ","> v_setlst('ODMS_ROW_WEIGHT_COLUMN_NAME') := '<row_weight_column_name>';"," ","* Specify a missing value treatment method for the training data. This setting does not affect the scoring data. The default value is `ODMS_MISSING_VALUE_AUTO`. The option `ODMS_MISSING_VALUE_MEAN_MODE` replaces missing values with the mean (numeric attributes) or the mode (categorical attributes) both at build time and apply time where appropriate. The option `ODMS_MISSING_VALUE_AUTO` performs different strategies for different algorithms. When `ODMS_MISSING_VALUE_TREATMENT` is set to `ODMS_MISSING_VALUE_DELETE_ROW`, the rows in the training data that contain missing values are deleted. However, if you want to replicate this missing value treatment in the scoring data, then you must perform the transformation explicitly.","> v_setlst('ODMS_MISSING_VALUE_TREATMENT') := 'ODMS_MISSING_VALUE_AUTO';","","#### These settings configure the behavior of the Random Forest algorithm ","","* Specify the number of trees in the forest. It requires a number between 1 and 65535 (including the edges). The default is 20.","> v_setlst('RFOR_NUM_TREES') := '20';","","* Specify the fraction of the training data to be randomly sampled for use in the construction of an individual tree. The default is half of the number of rows in the training data. It requires a fraction between 0 and 1 (excluding the edges)","> v_setlst('RFOR_SAMPLING_RATIO') := '0.5';","","* Specify the size of the random subset of columns to be considered when choosing a split at a node. For each node, the size of the pool remains the same, but the specific candidate columns change. The default is half of the columns in the model signature. The special value 0 indicates that the candidate pool includes all columns. It requires a number equal to or greater than 0.","> v_setlst('RFOR_MTRY') := '0';","","#### Decision Tree settings to configure the construction of individual trees."," ","* Specify Tree impurity metric for each Tree. "," Tree algorithms seek the best test question for splitting data at each node. The best splitter and split values are those that result in the largest increase in target value homogeneity (purity) for the entities in the node. Purity is by a metric. Decision trees can use either Gini `TREE_IMPURITY_GINI` or entropy `TREE_IMPURITY_ENTROPY` as the purity metric. By default, the algorithm uses `TREE_IMPURITY_GINI`.","> v_setlst('TREE_IMPURITY_METRIC') := 'TREE_IMPURITY_GINI';"," ","* Specify the criteria for splits regarding the maximum tree depth (the maximum number of nodes between the root and any leaf node, including the leaf node)."," For Decision Tree, it requires a number between 2 and 20, and the default is 7. For Random Forest it is a number between 2 and 100, and the default is 16.","> v_setlst('TREE_TERM_MAX_DEPTH') := '7';"," ","* Specify the minimum number of training rows in a node expressed as a percentage of the rows in the training data."," It requires a number between 0 and 10. The default is 0.05, indicating 0.05%. ","> v_setlst('TREE_TERM_MINPCT_NODE') := '0.05';"," ","* Specifyt he minimum number of rows required to consider splitting a node expressed as a percentage of the training rows."," It requires a number greater than 0, and smaller or equal to 20. The default is 0.1, indicating 0.1%. ","> v_setlst('TREE_TERM_MINPCT_SPLIT') := '0.1';","","* Specify The minimum number of rows in a node."," It requires a number greater than or equal to zero. The default is 10. ","> v_setlst('TREE_TERM_MINREC_NODE') := '10';"," ","* Specify the criteria for splits regarding the minimum number of records in a parent node expressed as a value. "," No split is attempted if the number of records is below this value. It requires a number greater than 1. The default is 20. ","> v_setlst('TREE_TERM_MINREC_SPLIT') := '20';"," ","* Specify the maximum number of bins for each attribute."," For Decision Tree it requires a number between 2 and 2,147,483,647, with the default value of 32. For Random Forest it requires a number between 2 and 254, with the default value of 32.","> v_setlst('CLAS_MAX_SUP_BINS') := '32'; "," "],"enabled":true,"result":{"startTime":1715350486381,"interpreter":"md.low","endTime":1715350486447,"results":[{"message":"<h3 id=\"examples-of-possible-setting-overrides-for-random-forest\">Examples of possible setting overrides for Random Forest<\/h3>\n<p>If the user does not override the default settings, then relevant settings are determined by the algorithm.<\/p>\n<p><strong>NOTE:<\/strong> Random Forest makes use of the Decision Tree settings to configure the construction of individual trees.<\/p>\n<p>A complete list of settings can be found in the Documentation link:<\/p>\n<ul>\n<li>\n<p><a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-481B6C67-B26E-4689-AD4C-98062D5A2117\" target=\"_blank\">Algorithm Settings - Random Forest<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-03435110-D723-42FD-B4EA-39C86A039566\" target=\"_blank\">Algorithm Settings - Decision Tree<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75\" target=\"_blank\">Shared Settings<\/a><\/p>\n<\/li>\n<li>\n<p>Specify a row weight column<\/p>\n<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('ODMS_ROW_WEIGHT_COLUMN_NAME') := '&lt;row_weight_column_name&gt;';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify a missing value treatment method for the training data. This setting does not affect the scoring data. The default value is <code>ODMS_MISSING_VALUE_AUTO<\/code>. The option <code>ODMS_MISSING_VALUE_MEAN_MODE<\/code> replaces missing values with the mean (numeric attributes) or the mode (categorical attributes) both at build time and apply time where appropriate. The option <code>ODMS_MISSING_VALUE_AUTO<\/code> performs different strategies for different algorithms. When <code>ODMS_MISSING_VALUE_TREATMENT<\/code> is set to <code>ODMS_MISSING_VALUE_DELETE_ROW<\/code>, the rows in the training data that contain missing values are deleted. However, if you want to replicate this missing value treatment in the scoring data, then you must perform the transformation explicitly.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('ODMS_MISSING_VALUE_TREATMENT') := 'ODMS_MISSING_VALUE_AUTO';<\/p>\n<\/blockquote>\n<h4 id=\"these-settings-configure-the-behavior-of-the-random-forest-algorithm\">These settings configure the behavior of the Random Forest algorithm<\/h4>\n<ul>\n<li>Specify the number of trees in the forest. It requires a number between 1 and 65535 (including the edges). The default is 20.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('RFOR_NUM_TREES') := '20';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the fraction of the training data to be randomly sampled for use in the construction of an individual tree. The default is half of the number of rows in the training data. It requires a fraction between 0 and 1 (excluding the edges)<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('RFOR_SAMPLING_RATIO') := '0.5';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the size of the random subset of columns to be considered when choosing a split at a node. For each node, the size of the pool remains the same, but the specific candidate columns change. The default is half of the columns in the model signature. The special value 0 indicates that the candidate pool includes all columns. It requires a number equal to or greater than 0.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('RFOR_MTRY') := '0';<\/p>\n<\/blockquote>\n<h4 id=\"decision-tree-settings-to-configure-the-construction-of-individual-trees\">Decision Tree settings to configure the construction of individual trees.<\/h4>\n<ul>\n<li>Specify Tree impurity metric for each Tree.\nTree algorithms seek the best test question for splitting data at each node. The best splitter and split values are those that result in the largest increase in target value homogeneity (purity) for the entities in the node. Purity is by a metric. Decision trees can use either Gini <code>TREE_IMPURITY_GINI<\/code> or entropy <code>TREE_IMPURITY_ENTROPY<\/code> as the purity metric. By default, the algorithm uses <code>TREE_IMPURITY_GINI<\/code>.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('TREE_IMPURITY_METRIC') := 'TREE_IMPURITY_GINI';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the criteria for splits regarding the maximum tree depth (the maximum number of nodes between the root and any leaf node, including the leaf node).\nFor Decision Tree, it requires a number between 2 and 20, and the default is 7. For Random Forest it is a number between 2 and 100, and the default is 16.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('TREE_TERM_MAX_DEPTH') := '7';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the minimum number of training rows in a node expressed as a percentage of the rows in the training data.\nIt requires a number between 0 and 10. The default is 0.05, indicating 0.05%.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('TREE_TERM_MINPCT_NODE') := '0.05';<\/p>\n<\/blockquote>\n<ul>\n<li>Specifyt he minimum number of rows required to consider splitting a node expressed as a percentage of the training rows.\nIt requires a number greater than 0, and smaller or equal to 20. The default is 0.1, indicating 0.1%.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('TREE_TERM_MINPCT_SPLIT') := '0.1';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify The minimum number of rows in a node.\nIt requires a number greater than or equal to zero. The default is 10.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('TREE_TERM_MINREC_NODE') := '10';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the criteria for splits regarding the minimum number of records in a parent node expressed as a value.\nNo split is attempted if the number of records is below this value. It requires a number greater than 1. The default is 20.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('TREE_TERM_MINREC_SPLIT') := '20';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the maximum number of bins for each attribute.\nFor Decision Tree it requires a number between 2 and 2,147,483,647, with the default value of 32. For Random Forest it requires a number between 2 and 254, with the default value of 32.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('CLAS_MAX_SUP_BINS') := '32';<\/p>\n<\/blockquote>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Build a Random Forest Model specifying additional settings","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('RF_CLASS_MODEL');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","DECLARE"," v_setlst DBMS_DATA_MINING.SETTING_LIST;"," ","BEGIN"," v_setlst('PREP_AUTO') := 'ON';"," v_setlst('ALGO_NAME') := 'ALGO_RANDOM_FOREST';"," v_setlst('RFOR_MTRY') := '3';"," v_setlst('RFOR_NUM_TREES') := '100';"," v_setlst('RFOR_SAMPLING_RATIO') := '0.5';"," "," DBMS_DATA_MINING.CREATE_MODEL2("," MODEL_NAME => 'RF_CLASS_MODEL',"," MINING_FUNCTION => 'CLASSIFICATION',"," DATA_QUERY => 'SELECT * FROM TRAIN_DATA_CLAS',"," SET_LIST => v_setlst,"," CASE_ID_COLUMN_NAME => 'CUST_ID',"," TARGET_COLUMN_NAME => 'AFFINITY_CARD'"," );","END;"],"enabled":true,"result":{"startTime":1715350486531,"interpreter":"script.low","endTime":1715350488528,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get list of model views","message":["%sql ","","SELECT VIEW_NAME, VIEW_TYPE ","FROM USER_MINING_MODEL_VIEWS","WHERE MODEL_NAME='RF_CLASS_MODEL'","ORDER BY VIEW_NAME;"],"enabled":true,"result":{"startTime":1715350488607,"interpreter":"sql.low","endTime":1715350488702,"results":[{"message":"VIEW_NAME\tVIEW_TYPE\nDM$VARF_CLASS_MODEL\tVariable Importance\nDM$VCRF_CLASS_MODEL\tScoring Cost Matrix\nDM$VGRF_CLASS_MODEL\tGlobal Name-Value Pairs\nDM$VSRF_CLASS_MODEL\tComputed Settings\nDM$VTRF_CLASS_MODEL\tClassification Targets\nDM$VWRF_CLASS_MODEL\tModel Build Alerts\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get attribute importance from RF model","message":["%sql","","SELECT ATTRIBUTE_NAME, round(ATTRIBUTE_IMPORTANCE, 3) IMPORTANCE ","FROM DM$VARF_CLASS_MODEL","ORDER BY ATTRIBUTE_IMPORTANCE DESC;"],"enabled":true,"result":{"startTime":1715350488781,"interpreter":"sql.low","endTime":1715350488875,"results":[{"message":"ATTRIBUTE_NAME\tIMPORTANCE\nYRS_RESIDENCE\t1.106\nHOUSEHOLD_SIZE\t0.955\nOCCUPATION\t0.901\nEDUCATION\t0.734\nY_BOX_GAMES\t0.469\nHOME_THEATER_PACKAGE\t0.418\nFLAT_PANEL_MONITOR\t0.359\nBOOKKEEPING_APPLICATION\t0.353\nBULK_PACK_DISKETTES\t0.308\nOS_DOC_SET_KANJI\t0\nPRINTER_SUPPLIES\t0\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get general settings","message":["%sql","","SELECT * from DM$VGRF_CLASS_MODEL;"],"enabled":true,"result":{"startTime":1715350488959,"interpreter":"sql.low","endTime":1715350489052,"results":[{"message":"PARTITION_NAME\tNAME\tNUMERIC_VALUE\tSTRING_VALUE\n\tNUM_ROWS\t2679\t\n\tMIN_NODECOUNT\t33\t\n\tMAX_NODECOUNT\t115\t\n\tAVG_NODECOUNT\t77.780000000000001\t\n\tMIN_DEPTH\t12\t\n\tMAX_DEPTH\t13\t\n\tAVG_DEPTH\t10.01\t\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get information for model settings","message":["%sql","","SELECT * FROM DM$VSRF_CLASS_MODEL"],"enabled":true,"result":{"startTime":1715350489129,"interpreter":"sql.low","endTime":1715350489206,"results":[{"message":"PARTITION_NAME\tSETTING_NAME\tSETTING_VALUE\n\tRFOR_MTRY\t3\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Evaluate the model","message":["%script","","BEGIN EXECUTE IMMEDIATE 'DROP TABLE APPLY_RESULT PURGE';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","BEGIN EXECUTE IMMEDIATE 'DROP TABLE LIFT_TABLE PURGE';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","BEGIN"," DBMS_DATA_MINING.APPLY('RF_CLASS_MODEL','TEST_DATA_CLAS','CUST_ID','APPLY_RESULT');"," DBMS_DATA_MINING.COMPUTE_LIFT('APPLY_RESULT','TEST_DATA_CLAS','CUST_ID','AFFINITY_CARD',"," 'LIFT_TABLE','1','PREDICTION','PROBABILITY',100);","END;"],"enabled":true,"result":{"startTime":1715350489281,"interpreter":"script.low","endTime":1715350490181,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":7,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"table\":{\"version\":1},\"area\":{\"showSeries\":[\"GAIN_CUMULATIVE\"],\"aggregationOption\":\"Sum\",\"series\":{\"availableSeriesElements\":[{\"id\":\"GAIN_CUMULATIVE\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"QUANTILE_NUMBER\",\"GAIN_CUMULATIVE\"],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"area","title":"View model's cumulative gains (lift) chart","message":["%sql","","SELECT QUANTILE_NUMBER, GAIN_CUMULATIVE FROM LIFT_TABLE;"],"enabled":true,"result":{"startTime":1715350490262,"interpreter":"sql.low","endTime":1715350490359,"results":[{"message":"QUANTILE_NUMBER\tGAIN_CUMULATIVE\n1\t3.93574312508824361445783132530120481928E-02\n2\t8.0722891566265060240963855421686746988E-02\n3\t1.15662650602409638554216867469879518072E-01\n4\t1.54654978556805346987951807228915662651E-01\n5\t1.84337349397590361445783132530120481928E-01\n6\t2.16265060240963855421686746987951807229E-01\n7\t2.54216867469879518072289156626506024096E-01\n8\t2.87817934334996240963855421686746987952E-01\n9\t3.11938660403332072289156626506024096386E-01\n10\t3.4874043292309865060240963855421686747E-01\n11\t3.81204811923475156626506024096385542169E-01\n12\t4.16599724091679228915662650602409638554E-01\n13\t4.50602409638554216867469879518072289157E-01\n14\t4.78915662650602409638554216867469879518E-01\n15\t5.02409638554216867469879518072289156627E-01\n16\t5.26193678522684481927710843373493975904E-01\n17\t5.48237407638365975903614457831325301205E-01\n18\t5.6345380300498869879518072289156626506E-01\n19\t5.84939759036144578313253012048192771084E-01\n20\t5.97590361445783132530120481927710843374E-01\n21\t6.24096385542168674698795180722891566265E-01\n22\t6.3775097950395330120481927710843373494E-01\n23\t6.50602409638554216867469879518072289157E-01\n24\t6.67099182864269590361445783132530120482E-01\n25\t6.93790518519390072289156626506024096386E-01\n26\t7.13623708701995469879518072289156626506E-01\n27\t7.22891566265060240963855421686746987952E-01\n28\t7.50602409638554216867469879518072289157E-01\n29\t7.78313253012048192771084337349397590361E-01\n30\t7.98072318571159638554216867469879518072E-01\n31\t8.08132530120481927710843373493975903615E-01\n32\t8.20331325301204819277108433734939759036E-01\n33\t8.36833025461219879518072289156626506024E-01\n34\t8.4819277108433734939759036144578313253E-01\n35\t8.61686761695218361445783132530120481928E-01\n36\t8.7309239399002260240963855421686746988E-01\n37\t8.81927710843373493975903614457831325301E-01\n38\t8.86746987951807228915662650602409638554E-01\n39\t8.9638554216867469879518072289156626506E-01\n40\t9.08433734939759036144578313253012048193E-01\n41\t9.08734939759036144578313253012048192771E-01\n42\t9.15180693477033132530120481927710843374E-01\n43\t9.20481927710843373493975903614457831325E-01\n44\t9.22891566265060240963855421686746987952E-01\n45\t9.25301204819277108433734939759036144578E-01\n46\t9.26907606009977397590361445783132530121E-01\n47\t9.34939759036144578313253012048192771084E-01\n48\t9.38554216867469879518072289156626506024E-01\n49\t9.39759036144578313253012048192771084337E-01\n50\t9.46987951807228915662650602409638554217E-01\n51\t9.48890263201242457831325301204819277108E-01\n52\t9.53660785721009036144578313253012048193E-01\n53\t9.59036144578313253012048192771084337349E-01\n54\t9.63253012048192771084337349397590361446E-01\n55\t9.64199645260730409638554216867469879518E-01\n56\t9.66265060240963855421686746987951807229E-01\n57\t9.66973803416792168674698795180722891566E-01\n58\t9.68674698795180722891566265060240963855E-01\n59\t9.71084337349397590361445783132530120482E-01\n60\t9.71084337349397590361445783132530120482E-01\n61\t9.73493975903614457831325301204819277108E-01\n62\t9.74216838055346385542168674698795180723E-01\n63\t9.78313253012048192771084337349397590361E-01\n64\t9.78313253012048192771084337349397590361E-01\n65\t9.78313253012048192771084337349397590361E-01\n66\t9.80722891566265060240963855421686746988E-01\n67\t9.80722891566265060240963855421686746988E-01\n68\t9.85542168674698795180722891566265060241E-01\n69\t9.85542168674698795180722891566265060241E-01\n70\t9.87951807228915662650602409638554216868E-01\n71\t9.87951807228915662650602409638554216868E-01\n72\t9.87951807228915662650602409638554216868E-01\n73\t9.90361445783132530120481927710843373494E-01\n74\t9.91265060240963855421686746987951807229E-01\n75\t9.92771084337349397590361445783132530121E-01\n76\t9.92771084337349397590361445783132530121E-01\n77\t9.95180722891566265060240963855421686747E-01\n78\t9.95180722891566265060240963855421686747E-01\n79\t9.97590361445783132530120481927710843374E-01\n80\t9.97590361445783132530120481927710843374E-01\n81\t9.97590361445783132530120481927710843374E-01\n82\t9.97590361445783132530120481927710843374E-01\n83\t9.97590361445783132530120481927710843374E-01\n84\t9.97590361445783132530120481927710843374E-01\n85\t9.99266622152673204819277108433734939759E-01\n86\t1\n87\t1\n88\t1\n89\t1\n90\t1\n91\t1\n92\t1\n93\t1\n94\t1\n95\t1\n96\t1\n97\t1\n98\t1\n99\t1\n100\t1\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":5,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display all customers > 70% likely to respond","message":["%sql","","SELECT CUST_ID, PREDICTION PRED, ROUND(PROBABILITY,3) PROB, ROUND(COST,2) COST "," FROM APPLY_RESULT WHERE PREDICTION = 1 AND PROBABILITY > 0.7"," ORDER BY PROBABILITY DESC;"," "],"enabled":true,"result":{"startTime":1715350490436,"interpreter":"sql.low","endTime":1715350490533,"results":[{"message":"CUST_ID\tPRED\tPROB\tCOST\n102330\t1\t0.822\t0.18\n102196\t1\t0.822\t0.18\n102037\t1\t0.822\t0.18\n101733\t1\t0.822\t0.18\n104149\t1\t0.822\t0.18\n101570\t1\t0.822\t0.18\n100883\t1\t0.807\t0.19\n100780\t1\t0.807\t0.19\n100442\t1\t0.807\t0.19\n101551\t1\t0.807\t0.19\n100476\t1\t0.803\t0.2\n104317\t1\t0.803\t0.2\n103256\t1\t0.803\t0.2\n102661\t1\t0.803\t0.2\n101449\t1\t0.803\t0.2\n101010\t1\t0.803\t0.2\n100052\t1\t0.789\t0.21\n101208\t1\t0.789\t0.21\n103836\t1\t0.787\t0.21\n102323\t1\t0.787\t0.21\n100815\t1\t0.787\t0.21\n104307\t1\t0.786\t0.21\n102326\t1\t0.786\t0.21\n101697\t1\t0.785\t0.22\n102734\t1\t0.785\t0.22\n104206\t1\t0.785\t0.22\n100179\t1\t0.785\t0.22\n101559\t1\t0.785\t0.22\n101086\t1\t0.785\t0.22\n102230\t1\t0.783\t0.22\n102783\t1\t0.781\t0.22\n100390\t1\t0.778\t0.22\n101763\t1\t0.778\t0.22\n102573\t1\t0.778\t0.22\n103294\t1\t0.778\t0.22\n102959\t1\t0.763\t0.24\n102142\t1\t0.763\t0.24\n102033\t1\t0.763\t0.24\n101952\t1\t0.763\t0.24\n101935\t1\t0.763\t0.24\n103439\t1\t0.763\t0.24\n100760\t1\t0.761\t0.24\n104251\t1\t0.761\t0.24\n103837\t1\t0.761\t0.24\n103232\t1\t0.761\t0.24\n102380\t1\t0.761\t0.24\n101581\t1\t0.761\t0.24\n101485\t1\t0.761\t0.24\n101031\t1\t0.761\t0.24\n100906\t1\t0.761\t0.24\n100816\t1\t0.761\t0.24\n100034\t1\t0.761\t0.24\n100012\t1\t0.759\t0.24\n103428\t1\t0.747\t0.25\n100633\t1\t0.747\t0.25\n102615\t1\t0.747\t0.25\n100988\t1\t0.712\t0.29\n101421\t1\t0.712\t0.29\n100701\t1\t0.712\t0.29\n102022\t1\t0.712\t0.29\n103361\t1\t0.712\t0.29\n104093\t1\t0.712\t0.29\n102214\t1\t0.711\t0.29\n102767\t1\t0.707\t0.29\n100344\t1\t0.702\t0.3\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Select and view likely and unlikely responders ","message":["%sql ","","SELECT CUST_ID, PREDICTION, ROUND(PROBABILITY,2) PROB, ROUND(COST,2) COST"," FROM APPLY_RESULT WHERE PREDICTION = ${PREDICTION='1','1'|'0'} "," AND PROBABILITY > 0.7 ORDER BY PROBABILITY DESC;"," "],"enabled":true,"result":{"startTime":1715350490612,"interpreter":"sql.low","endTime":1715350490706,"results":[{"message":"CUST_ID\tPREDICTION\tPROB\tCOST\n102330\t1\t0.82\t0.18\n102196\t1\t0.82\t0.18\n102037\t1\t0.82\t0.18\n101733\t1\t0.82\t0.18\n104149\t1\t0.82\t0.18\n101570\t1\t0.82\t0.18\n100883\t1\t0.81\t0.19\n100780\t1\t0.81\t0.19\n100442\t1\t0.81\t0.19\n101551\t1\t0.81\t0.19\n100476\t1\t0.8\t0.2\n104317\t1\t0.8\t0.2\n103256\t1\t0.8\t0.2\n102661\t1\t0.8\t0.2\n101449\t1\t0.8\t0.2\n101010\t1\t0.8\t0.2\n100052\t1\t0.79\t0.21\n101208\t1\t0.79\t0.21\n103836\t1\t0.79\t0.21\n102323\t1\t0.79\t0.21\n100815\t1\t0.79\t0.21\n104307\t1\t0.79\t0.21\n102326\t1\t0.79\t0.21\n101697\t1\t0.78\t0.22\n102734\t1\t0.78\t0.22\n104206\t1\t0.78\t0.22\n100179\t1\t0.78\t0.22\n101559\t1\t0.78\t0.22\n101086\t1\t0.78\t0.22\n102230\t1\t0.78\t0.22\n102783\t1\t0.78\t0.22\n100390\t1\t0.78\t0.22\n101763\t1\t0.78\t0.22\n102573\t1\t0.78\t0.22\n103294\t1\t0.78\t0.22\n102959\t1\t0.76\t0.24\n102142\t1\t0.76\t0.24\n102033\t1\t0.76\t0.24\n101952\t1\t0.76\t0.24\n101935\t1\t0.76\t0.24\n103439\t1\t0.76\t0.24\n100760\t1\t0.76\t0.24\n104251\t1\t0.76\t0.24\n103837\t1\t0.76\t0.24\n103232\t1\t0.76\t0.24\n102380\t1\t0.76\t0.24\n101581\t1\t0.76\t0.24\n101485\t1\t0.76\t0.24\n101031\t1\t0.76\t0.24\n100906\t1\t0.76\t0.24\n100816\t1\t0.76\t0.24\n100034\t1\t0.76\t0.24\n100012\t1\t0.76\t0.24\n103428\t1\t0.75\t0.25\n100633\t1\t0.75\t0.25\n102615\t1\t0.75\t0.25\n100988\t1\t0.71\t0.29\n101421\t1\t0.71\t0.29\n100701\t1\t0.71\t0.29\n102022\t1\t0.71\t0.29\n103361\t1\t0.71\t0.29\n104093\t1\t0.71\t0.29\n102214\t1\t0.71\t0.29\n102767\t1\t0.71\t0.29\n100344\t1\t0.7\t0.3\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":"{\"PREDICTION\":\"'1'\"}","row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[{\"type\":\"Select\",\"name\":\"PREDICTION\",\"displayName\":null,\"defaultValue\":\"'1'\",\"argument\":null,\"options\":[{\"value\":\"'1'\",\"displayName\":null},{\"value\":\"'0'\",\"displayName\":null}],\"isHidden\":false,\"isProgrammatic\":false}]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Dynamically list customers >70% likely to respond","message":["%sql","","SELECT * ","FROM (SELECT CUST_ID, "," round(PREDICTION_PROBABILITY(RF_CLASS_MODEL, '1' USING A.*), 3) PROBABILITY"," FROM TEST_DATA_CLAS A)","WHERE PROBABILITY > 0.7","ORDER BY PROBABILITY","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1715350490781,"interpreter":"sql.low","endTime":1715350491430,"results":[{"message":"CUST_ID\tPROBABILITY\n100344\t0.702\n102767\t0.707\n102214\t0.711\n100701\t0.712\n104093\t0.712\n103361\t0.712\n102022\t0.712\n101421\t0.712\n100988\t0.712\n102615\t0.747\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"raw\":{\"height\":300,\"lastColumns\":[],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"raw","title":"Dynamically list customers >70% likely to respond","message":["%sql","","SELECT CUST_ID","FROM TEST_DATA_CLAS","WHERE PREDICTION_PROBABILITY(RF_CLASS_MODEL, '1' USING *) > 0.7;","FETCH FIRST 10 ROWS ONLY"],"enabled":true,"result":{"startTime":1715350491507,"interpreter":"sql.low","endTime":1715350492078,"results":[{"message":"CUST_ID\n102033\n102615\n100344\n100815\n100906\n101485\n101733\n102330\n103836\n104251\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Apply model to a single record","message":["%sql","","-- Predict the probability of being a high AFFINITY_CARD responder (1) ","-- by providing specific attribute values for a customer.","","SELECT ROUND(PREDICTION_PROBABILITY(RF_CLASS_MODEL, '1' USING "," '3' AS HOUSEHOLD_SIZE, "," 5 AS YRS_RESIDENCE, "," 1 AS Y_BOX_GAMES),3) PROBABILITY_RESPONDER","FROM DUAL;"," "],"enabled":true,"result":{"startTime":1715350492158,"interpreter":"sql.low","endTime":1715350492232,"results":[{"message":"PROBABILITY_RESPONDER\n0.305\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Dynamically score all customers","message":["%sql","","SELECT CUST_ID,"," PREDICTION(RF_CLASS_MODEL USING *) PREDICTION_AFFINITY_CARD,"," ROUND(PREDICTION_PROBABILITY(RF_CLASS_MODEL USING *), 3) PROBABILITY","FROM TEST_DATA_CLAS","ORDER BY CUST_ID","FETCH FIRST 10 ROWS ONLY;"," "],"enabled":true,"result":{"startTime":1715350492311,"interpreter":"sql.low","endTime":1715350492408,"results":[{"message":"CUST_ID\tPREDICTION_AFFINITY_CARD\tPROBABILITY\n100004\t0\t0.915\n100009\t0\t0.637\n100011\t0\t0.964\n100012\t1\t0.759\n100013\t0\t0.528\n100014\t0\t0.88\n100018\t0\t0.839\n100026\t0\t0.554\n100027\t0\t0.809\n100033\t0\t0.915\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Get prediction details","message":["%sql","","SELECT CUST_ID,"," round(PREDICTION_AFFINITY_CARD,3) PRED,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute1\",17,100)),'rank=\"1\"/>') FIRST_ATTRIBUTE,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute2\",17,100)),'rank=\"2\"/>') SECOND_ATTRIBUTE","FROM (SELECT CUST_ID,"," PREDICTION(RF_CLASS_MODEL USING *) PREDICTION_AFFINITY_CARD,"," PREDICTION_DETAILS(RF_CLASS_MODEL USING *) PD"," FROM TEST_DATA_CLAS"," WHERE CUST_ID < 100025"," ORDER BY CUST_ID) OUT,"," XMLTABLE('/Details' PASSING OUT.PD COLUMNS "," \"Attribute1\" XMLType PATH 'Attribute[1]',"," \"Attribute2\" XMLType PATH 'Attribute[2]') OUTPRED","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1715350492482,"interpreter":"sql.low","endTime":1715350492578,"results":[{"message":"CUST_ID\tPRED\tFIRST_ATTRIBUTE\tSECOND_ATTRIBUTE\n100004\t0\t\"FLAT_PANEL_MONITOR\" actualValue=\"1\" weight=\".822\" \t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".157\" \n100009\t0\t\"Y_BOX_GAMES\" actualValue=\"1\" weight=\"13.078\" \t\"YRS_RESIDENCE\" actualValue=\"3\" weight=\"4.37\" \n100011\t0\t\"YRS_RESIDENCE\" actualValue=\"3\" weight=\"4.745\" \t\"HOME_THEATER_PACKAGE\" actualValue=\"0\" weight=\"2.305\" \n100012\t1\t\"Y_BOX_GAMES\" actualValue=\"0\" weight=\"2.515\" \t\"HOME_THEATER_PACKAGE\" actualValue=\"1\" weight=\"1.931\" \n100013\t0\t\"EDUCATION\" actualValue=\"HS-grad\" weight=\".046\" \t\"OCCUPATION\" actualValue=\"Transp.\" weight=\".041\" \n100014\t0\t\"BULK_PACK_DISKETTES\" actualValue=\"0\" weight=\".235\" \t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".161\" \n100018\t0\t\"Y_BOX_GAMES\" actualValue=\"1\" weight=\"8.169\" \t\"YRS_RESIDENCE\" actualValue=\"3\" weight=\"4.641\" \n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","## Create Classification model using a settings table","","The settings table is an alternative way to specify algorithm settings to build the model. We first drop the settings table, create it, and then populate it with settings before building the model."],"enabled":true,"result":{"startTime":1715350492662,"interpreter":"md.low","endTime":1715350492719,"results":[{"message":"<h2 id=\"create-classification-model-using-a-settings-table\">Create Classification model using a settings table<\/h2>\n<p>The settings table is an alternative way to specify algorithm settings to build the model. We first drop the settings table, create it, and then populate it with settings before building the model.<\/p>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Create settings table and build classification model","message":["%script","","BEGIN EXECUTE IMMEDIATE 'DROP TABLE N1_BUILD_SETTINGS PURGE';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","CREATE TABLE N1_BUILD_SETTINGS (setting_name VARCHAR2(30),setting_value VARCHAR2(4000));","/","BEGIN DBMS_DATA_MINING.DROP_MODEL('CLASS_MODEL_2');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","","BEGIN"," INSERT INTO N1_BUILD_SETTINGS (setting_name, setting_value) VALUES ('ALGO_NAME', 'ALGO_RANDOM_FOREST');"," INSERT INTO N1_BUILD_SETTINGS (setting_name, setting_value) VALUES ('PREP_AUTO', 'ON');",""," DBMS_DATA_MINING.CREATE_MODEL('CLASS_MODEL_2', 'CLASSIFICATION', 'TRAIN_DATA_CLAS', 'CUST_ID',' AFFINITY_CARD', 'N1_BUILD_SETTINGS');","END;"],"enabled":true,"result":{"startTime":1715350492797,"interpreter":"script.low","endTime":1715350494395,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nTable N1_BUILD_SETTINGS created.\n\n\n---------------------------\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","## End of Script"],"enabled":true,"result":{"startTime":1715350494474,"interpreter":"md.low","endTime":1715350494534,"results":[{"message":"<h2 id=\"end-of-script\">End of Script<\/h2>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":[],"enabled":true,"result":{"startTime":1715350494611,"interpreter":"md.low","endTime":1715350494670,"results":[],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":true,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"}],"version":"6","snapshot":false,"tags":null}]